﻿--成单跟踪列表
CREATE PROCEDURE [dbo].[proc_Orders_Track_GetList]
	
	@StartIndex int,
	@EndIndex int,
	@CompanyId int,
	@MainProjectId int,
	@SText nvarchar(50),
	@StartDate nvarchar(50),
	@EndDate nvarchar (50),
	@State int, --全部 2 完成 1 未完成 0
	@FpState int
	
as
begin
;WITH list As(Select ROW_NUMBER() OVER (ORDER BY o.Id )AS Row,
	o.id,
	o.oid,
	o.customerId,
	isnull((select customer.customername from customer where customer.customerid=o.customerId),'...') as customername,
	(select e.ename from employee e where e.username=o.username) as ename,
	--o.FixtureDate,
	IsNull((select top 1 project.createdate from project where project.orderid=o.oid order by project.id desc),'1900-01-01') as FixtureDate,
	(select product.title from product where product.id=o.MainProjectId) AS MainProjectName,
	(select e.ename from employee e where e.username=o.trackkf) as trackkf,
	(select count(project.id) from project where project.orderid=o.oid) as ProjectCount,
	(select count(project.id) from project where project.orderid=o.oid and project.isfinish=1) as ProjectFinishCount
from 
	orders o
where
	companyid=@companyid
	and (Convert(varchar(10),(IsNull((select top 1 project.createdate from project where project.orderid=o.oid order by project.id desc),'1900-01-01')),120)>=@StartDate and Convert(varchar(10),(IsNull((select top 1 project.createdate from project where project.orderid=o.oid order by project.id desc),'1900-01-01')),120)<=@EndDate)
	and ((select e.ename from employee e where e.username=o.trackkf) like  '%'+@SText+'%' or isnull((Select CustomerName From Customer Where CustomerId=o.CustomerId),'...') like '%'+@SText+'%')
	and (@MainProjectId=0 or MainProjectId=@MainProjectId)
	and ((case when ((select count(project.id) from project where project.orderid=o.oid)>0) and  (select count(project.id) from project where project.orderid=o.oid and project.isfinish=0 )=0 then 1 else 0 end ) = @state or @state=2)
	and ((case when o.trackkf is null then 0 else 1 end) = @FpState or @FpState=2)
)

Select * From list Where Row between @StartIndex and @EndIndex --order by ProjectCount desc,trackkf desc
	end
	RETURN
